
import time
!pip install shap
!pip install phik
import shap
import datetime
import phik

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px


from sqlalchemy import create_engine

from sklearn.impute import SimpleImputer
from sklearn.model_selection import train_test_split, cross_val_score, GridSearchCV
from sklearn.preprocessing import StandardScaler, OneHotEncoder # ,  OrdinalEncoder

from sklearn.metrics import accuracy_score, recall_score, f1_score, roc_auc_score, roc_curve, precision_score
from sklearn.metrics import confusion_matrix, ConfusionMatrixDisplay, classification_report

from sklearn.tree import DecisionTreeClassifier
from sklearn.ensemble import RandomForestClassifier
from sklearn.linear_model import LogisticRegression



import lightgbm as lgb
from lightgbm import LGBMClassifier

import catboost
from catboost import CatBoostClassifier


import warnings
warnings.filterwarnings('ignore')
pd.options.display.max_columns = None

RANDOM_STATE=12345


#база данных чужая

db_config = {
'user': 'user', 
'pwd': 'pwd', 
'host': 'host',
'port': 'port', 
'db': 'db' 
}


connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(
    db_config['user'],
    db_config['pwd'],
    db_config['host'],
    db_config['port'],
    db_config['db'],
) 
engine = create_engine(connection_string)


collisions = '''

SELECT *
FROM collisions
LIMIT 10
'''

parties = '''

SELECT *
FROM parties
LIMIT 10
'''

vehicles = '''

SELECT *
FROM vehicles
LIMIT 10
'''

case_ids = '''

SELECT *
FROM case_ids
LIMIT 10
'''


pd.read_sql_query('''
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema');
''', con = engine)


pd.read_sql_query('''
SELECT
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_name IN ('case_ids', 'collisions', 'parties', 'vehicles');
''', con=engine)


collisions = pd.read_sql_query(collisions, con = engine)
collisions


parties = pd.read_sql_query(parties, con = engine)
parties


vehicles = pd.read_sql_query(vehicles, con = engine)
vehicles


case_ids = pd.read_sql_query(case_ids, con = engine)
case_ids


query = '''
SELECT constraint_name,
       table_name,
       column_name
  FROM information_schema.key_column_usage
'''
df = pd.read_sql_query(query, con=engine)
df


query = '''
with a as (SELECT COUNT(case_id) AS total_ids,
       DATE_TRUNC('month', collision_date)::date AS month
       
  FROM collisions
 GROUP BY DATE_TRUNC('month', collision_date)
 ORDER BY DATE_TRUNC('month', collision_date))
SELECT *,
    EXTRACT(year FROM CAST(DATE_TRUNC('year', month) as DATE)) AS year,
    EXTRACT(month FROM CAST(DATE_TRUNC('month', month) as DATE)) AS month_only
FROM a 
 ;
'''
df = pd.read_sql_query(query, con=engine)


fig = px.bar(
    df, x='month', y='total_ids',
    barmode='group',
    title=('Статистика дтп по месяцам')
)
fig.show()


dt = datetime.date(2012, 1, 1)
df.loc[df['month']>=dt]


query = """

WITH f AS
(SELECT CAST(DATE_TRUNC('MONTH', COLLISION_DATE) as DATE) AS date,
       COUNT(CASE_ID)
FROM collisions
WHERE COLLISION_DATE BETWEEN '2009-01-01' AND '2011-12-31'
GROUP BY CAST(DATE_TRUNC('MONTH', COLLISION_DATE) as DATE))

SELECT EXTRACT(MONTH FROM date) AS month,
       AVG(count) AS total_ids
FROM f

GROUP BY EXTRACT(MONTH FROM date);

"""
df_mid = pd.read_sql_query(query, con = engine)


df_mid = df_mid.sort_values(by = 'month')
df = df.sort_values(by = 'month')


month_list = {1 : 'Январь',
              2 : 'Февраль',
              3 : 'Март',
              4 : 'Апрель',
              5 : 'Май',
              6 : 'Июнь',
              7 : 'Июль',
              8 : 'Август',
              9 : 'Сентябрь',
              10 : 'Октябрь',
              11 : 'Ноябрь',
              12 : 'Декабрь'}


df['month_only'] = df['month_only'].replace(month_list)
df_mid['month'] = df_mid['month'].replace(month_list)


fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2009]['total_ids'], label='2009')
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2010]['total_ids'], label='2010')
ax.plot(df.loc[df['year']==2009]['month_only'], df.loc[df['year']==2011]['total_ids'], label='2011')

ax.plot(df_mid['month'], df_mid['total_ids'], linewidth=16, alpha=0.3, label='Среднее')
ax.set_title('ДТП втечение года', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Количество проишествий')
ax.legend()
plt.grid(linestyle='--')
plt.show()


query = '''

SELECT COUNTY_LOCATION,
       COUNT(CASE_ID)
FROM collisions
GROUP BY COUNTY_LOCATION
ORDER BY COUNT(CASE_ID) DESC;

'''

df = pd.read_sql_query(query, con = engine)
df.sample(8)


plt.figure(figsize=(8, 15))
sns.barplot(data=df, y="county_location", x="count", orient='h', color='g')
plt.title('Районы с наибольшим количеством ДТП', fontsize=15)
#plt.xticks( fontsize=10)
#plt.yticks( fontsize=10)
plt.ylabel('Наименование района')
plt.xlabel('Количество проишествий')
plt.grid(True)
plt.show()


df = '''

WITH 

c1 AS 
(SELECT COUNTY_LOCATION,
       COUNT(CASE_ID)
FROM collisions
GROUP BY COUNTY_LOCATION
ORDER BY COUNT(CASE_ID) DESC),

c2 AS
(SELECT ROW_NUMBER() OVER (PARTITION BY COUNTY_LOCATION ORDER BY COUNT(CASE_ID) DESC),
        COUNT(CASE_ID),
        COUNTY_LOCATION,
        PCF_VIOLATION_CATEGORY
FROM collisions
GROUP BY COUNTY_LOCATION, PCF_VIOLATION_CATEGORY
ORDER BY COUNTY_LOCATION)

SELECT COUNTY_LOCATION,
       PCF_VIOLATION_CATEGORY,
       COUNT
FROM c2
WHERE ROW_NUMBER <= 3 AND COUNTY_LOCATION IN (SELECT COUNTY_LOCATION
                                          FROM c1
                                          LIMIT 8);


'''
df = pd.read_sql_query(df, con = engine)
df


plt.figure(figsize=(15, 8))
sns.barplot(data=df, x="county_location", y="count", hue = 'pcf_violation_category')
plt.title('Категории нарушений по самым аварийным регионам', fontsize=20)
#plt.xticks(rotation=90)
plt.xlabel('Наименование района')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()


query = '''
SELECT c.collision_damage,
       p.party_drug_physical
  FROM collisions AS c
       JOIN parties AS p ON c.case_id = p.case_id
WHERE c.collision_damage IN ('small damage', 'scratch')

'''
df = pd.read_sql_query(query, con=engine)
df.sample(4)


df.isna().sum()

collision_damage             0
party_drug_physical    1975744
dtype: int64


df = df.dropna()


query = '''
SELECT c.collision_damage,
       p.party_drug_physical
  FROM collisions AS c
       JOIN parties AS p ON c.case_id = p.case_id
WHERE c.collision_damage IN ('fatal', 'severe damage', 'middle damage')

'''
df2 = pd.read_sql_query(query, con=engine)
df2.sample(4)


df2.isna().sum()

collision_damage            0
party_drug_physical    344376
dtype: int64


df2 = df2.dropna()


fig = px.histogram(
    df, color='party_drug_physical', x='collision_damage',
    barmode='group',
    title='Зависимость тяжести ДТП от физического состояния водителя',
    histfunc='count'
)
fig.show()


fig = px.histogram(
    df2, color='party_drug_physical', x='collision_damage',
    barmode='group',
    title='Зависимость тяжести ДТП от физического состояния водителя (количество)',
    histfunc='count'
)
fig.show()


query = '''
SELECT c.COUNTY_LOCATION,       
        COUNT(c.CASE_ID)
       
FROM collisions AS c
    JOIN parties AS p ON c.case_id = p.case_id
WHERE p.PARTY_SOBRIETY in ('had been drinking, under influence',
       'had been drinking, impairment unknown',
       'had been drinking, not under influence')    
GROUP BY c.COUNTY_LOCATION
order by COUNT desc

'''
df = pd.read_sql_query(query, con=engine)
df.head(8)


plt.figure(figsize=(8,15))
sns.barplot(data=df, y="county_location", x="count", orient='h', color='g')
plt.title('Районы с наибольшим количеством ДТП с участием водителей в состоянии алкогольного опьянения', fontsize=12)

plt.ylabel('Наименование района')
plt.xlabel('Количество проишествий')
plt.grid(True)
plt.show()


query = '''
with a as
(SELECT c.COUNTY_LOCATION,
       p.PARTY_SOBRIETY,
       c.CASE_ID
       
FROM collisions AS c
    JOIN parties AS p ON c.case_id = p.case_id),
    
b as 
(select COUNT(CASE_ID),
        COUNTY_LOCATION,
        PARTY_SOBRIETY  

FROM a
WHERE PARTY_SOBRIETY in ('had been drinking, under influence',
       'had been drinking, impairment unknown',
       'had been drinking, not under influence')
GROUP BY COUNTY_LOCATION, PARTY_SOBRIETY),

c as
(select COUNTY_LOCATION, sum(count)
from b
GROUP BY COUNTY_LOCATION
order by sum desc
limit 8
)

select
COUNT,
        COUNTY_LOCATION,
        PARTY_SOBRIETY
from b
where COUNTY_LOCATION in (select county_location from c)


'''
df = pd.read_sql_query(query, con=engine)


plt.figure(figsize=(15, 8))
sns.barplot(data=df, x="county_location", y="count", hue = 'party_sobriety')
plt.title('Регионы с максимальным количеством аварий с участием водителей в состоянии алкогольного опьянения', fontsize=12)
#plt.xticks(rotation=90)
plt.xlabel('Наименование района')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()


plt.figure(figsize=(15, 8))
sns.barplot(data=df, hue="county_location", y="count", x = 'party_sobriety')
plt.title('Регионы с максимальным количеством аварий с участием водителей в состоянии алкогольного опьянения', fontsize=12)
#plt.xticks(rotation=90)
plt.xlabel('Вид опьянения')
plt.ylabel('Количество проишествий')
plt.grid(True)
plt.show()


query = """
with foltdir as (
select p.at_fault,
        c.direction
from parties as p
join collisions as c on p.case_id=c.case_id
WHERE extract(hour from COLLISION_TIME) in (5, 6, 7, 8, 17, 18,19, 20)
)

select sum(at_fault),
    direction
from foltdir
WHERE direction is NOT null
group by direction
"""
df = pd.read_sql_query(query, con = engine)

df


fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)

ax.plot(df['direction'], df['sum'])
ax.set_title('Временной ряд ДТП', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Среднее количество проишествий')

plt.show()


query = """
with foltdir as (
select p.at_fault,
c.direction
from parties as p
join collisions as c on p.case_id=c.case_id
WHERE LIGHTING in ('dusk or dawn') 
)

select sum(at_fault),
 direction
from foltdir
WHERE direction is NOT null
group by direction
"""
df = pd.read_sql_query(query, con = engine)


fig, ax = plt.subplots()
fig.set_figwidth(15)
fig.set_figheight(7)

ax.plot(df['direction'], df['sum'])
ax.set_title('Временной ряд ДТП', fontsize=20)
ax.set_xlabel('Месяц проишествия')
ax.set_ylabel('Среднее количество проишествий')

plt.show()


query = '''
select c.collision_damage,
        v.vehicle_transmission
from vehicles as v
join collisions as c on v.case_id=c.case_id
where v.vehicle_transmission is not null
'''
df = pd.read_sql_query(query, con=engine)


fig = px.histogram(
    df, x='collision_damage', color='vehicle_transmission',
    barmode='group',
    title='Зависимость тяжести ДТП от типа трансмиссии',
    histfunc='count',
    category_orders={
        'vehicle_transmission': ['auto', 'manual']
    }
)

fig.show()


query = '''


with VPS as
(SELECT v.case_id,
        v.party_number,
        v.vehicle_type,
        v.vehicle_transmission,
        v.vehicle_age,
        p.party_type,
        p.at_fault,
        p.insurance_premium,
        p.party_sobriety,
        p.party_drug_physical,
        p.cellphone_in_use
FROM vehicles AS v 
INNER JOIN parties AS p ON (p.CASE_ID = v.CASE_ID AND p.PARTY_NUMBER = v.PARTY_NUMBER))

SELECT *,
    extract(hour from collision_time) as collision_time_2,
    extract(month from COLLISION_DATE) as COLLISION_DATE_2
FROM case_ids AS ci
INNER JOIN VPS AS v ON ci.CASE_ID = v.CASE_ID
INNER JOIN collisions AS c ON ci.CASE_ID = c.CASE_ID
WHERE v.PARTY_TYPE = 'car' AND c.COLLISION_DAMAGE != 'SCRATCH' AND EXTRACT(YEAR FROM c.COLLISION_DATE) = 2012;

'''

df = pd.read_sql_query(query, con=engine)

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 103267 entries, 0 to 103266
Data columns (total 35 columns):
 #   Column                       Non-Null Count   Dtype  
---  ------                       --------------   -----  
 0   case_id                      103267 non-null  object 
 1   db_year                      103267 non-null  object 
 2   case_id                      103267 non-null  object 
 3   party_number                 103267 non-null  int64  
 4   vehicle_type                 103267 non-null  object 
 5   vehicle_transmission         101309 non-null  object 
 6   vehicle_age                  100606 non-null  float64
 7   party_type                   103267 non-null  object 
 8   at_fault                     103267 non-null  int64  
 9   insurance_premium            102471 non-null  float64
 10  party_sobriety               101673 non-null  object 
 11  party_drug_physical          4340 non-null    object 
 12  cellphone_in_use             93264 non-null   float64
 13  case_id                      103267 non-null  object 
 14  county_city_location         103267 non-null  object 
 15  county_location              103267 non-null  object 
 16  distance                     103267 non-null  float64
 17  direction                    75818 non-null   object 
 18  intersection                 102918 non-null  float64
 19  weather_1                    102962 non-null  object 
 20  location_type                43323 non-null   object 
 21  collision_damage             103267 non-null  object 
 22  party_count                  103267 non-null  int64  
 23  primary_collision_factor     102992 non-null  object 
 24  pcf_violation_category       101786 non-null  object 
 25  type_of_collision            102672 non-null  object 
 26  motor_vehicle_involved_with  102926 non-null  object 
 27  road_surface                 102484 non-null  object 
 28  road_condition_1             102722 non-null  object 
 29  lighting                     102934 non-null  object 
 30  control_device               102791 non-null  object 
 31  collision_date               103267 non-null  object 
 32  collision_time               103157 non-null  object 
 33  collision_time_2             103157 non-null  float64
 34  collision_date_2             103267 non-null  float64
dtypes: float64(7), int64(3), object(25)
memory usage: 27.6+ MB


df.describe().transpose()


begin_len_df = len(df)


df.drop(columns=['case_id', 'party_number', 'party_type', 'db_year'], inplace=True, errors='ignore')


df.sample(8).transpose()


df.duplicated().sum()

38


df.drop_duplicates(inplace=True, ignore_index=True)


df.rename(columns={'weather_1':'weather', 'road_condition_1':'road_condition', 'collision_date_2':'collision_month',
                  'collision_time_2':'collision_hour'}, inplace=True)


df_na = pd.DataFrame(df.isna().sum(), columns=['isna'])


df_na['%'] = round(df_na['isna']*100/len(df), 0)


df_na.sort_values(by='%', ascending=False)


columns = df.columns


for column in columns:
    
    print(f'\n\nУникальные значения параметра {column}\n\n')
    print(df[column].unique())


Уникальные значения параметра vehicle_type


['sedan' 'coupe' 'other']


Уникальные значения параметра vehicle_transmission


['auto' 'manual' None]


Уникальные значения параметра vehicle_age


[  9.   3.   8.   1.   4.  11.   5.   6.  13.   0.   2.   7.  12.  nan
  14.  10.  15.  16.  19.  17. 161.]


Уникальные значения параметра at_fault


[1 0]


Уникальные значения параметра insurance_premium


[ 61.  nan  19.  33.  20.  44.  57.  43.  45.  27.  64.  90.  21.  34.
  28.  31.  29.  17.  37.  39.  18.  36.  22.  24.  74.  25.  75.  67.
  26.  51.  56.  66.  50.  91.  54.  53.  23.  46.  32.  62.  78.  47.
  77.  48.  55.  63.  41.  86.  85.  40.  16.  35.  30.  38.  52.  15.
  72.  89.  80.  49.  68.  42.  73.  65.  60.  71.  81.  84.  83.  58.
  69.  76.  70.  79.  59.  93.   6.   0.  82.  87.  12.  13.  88.  95.
  92.   8.  14.  11.  94.  97.   7.   2.  10. 102.  96.  98.   4.   9.
 104.  99.   5. 100. 105. 103. 101.]


Уникальные значения параметра party_sobriety


['had not been drinking' 'impairment unknown'
 'had been drinking, under influence' None
 'had been drinking, impairment unknown'
 'had been drinking, not under influence' 'not applicable']


Уникальные значения параметра party_drug_physical


[None 'G' 'sleepy/fatigued' 'under drug influence' 'impairment - physical'
 'not applicable']


Уникальные значения параметра cellphone_in_use


[ 0. nan  1.]


Уникальные значения параметра county_city_location


['1900' '4116' '1942' '3801' '4806' '3313' '3711' '5607' '2900' '3300'
 '4807' '3010' '4314' '2500' '0704' '3009' '1955' '3400' '1948' '3612'
 '3720' '0900' '5608' '3006' '5800' '3050' '0800' '2106' '0403' '5704'
 '3616' '3702' '1953' '1205' '4312' '3404' '3605' '3310' '2000' '4313'
 '3001' '5700' '3700' '1300' '3025' '0402' '1200' '3007' '3312' '1932'
 '4400' '3344' '2800' '3003' '4402' '1000' '3311' '3603' '1949' '4600'
 '0710' '2300' '0107' '3024' '1503' '1992' '3600' '0700' '3607' '1700'
 '1941' '1500' '4109' '5002' '1969' '3012' '3015' '2802' '3302' '3019'
 '1912' '1925' '3105' '5601' '3640' '4316' '1909' '0400' '5600' '3022'
 '4203' '3026' '4303' '1973' '5001' '1985' '0198' '0712' '1916' '5500'
 '3045' '1962' '5703' '3703' '5609' '4403' '1603' '1938' '4006' '3394'
 '1502' '4200' '3609' '3604' '4803' '0303' '1906' '4110' '3706' '5000'
 '1100' '1994' '3900' '3903' '1203' '0109' '2406' '3342' '3610' '4904'
 '1914' '2908' '3100' '4500' '3008' '4307' '2708' '1920' '3017' '4000'
 '4907' '3016' '0405' '3710' '1943' '1933' '1950' '4700' '5100' '4302'
 '4100' '2401' '1975' '4903' '3301' '1995' '1902' '0111' '3020' '1963'
 '1915' '3309' '1976' '4900' '5400' '0105' '4113' '0701' '1903' '1965'
 '1979' '2700' '4502' '1005' '4310' '3000' '0113' '4800' '3783' '2706'
 '2805' '1919' '3343' '3018' '1993' '5690' '3450' '0734' '3701' '3619'
 '0101' '1937' '2901' '1401' '3713' '1908' '1907' '0100' '4308' '3602'
 '3709' '5604' '1922' '3782' '1956' '1946' '4908' '4204' '1977' '3615'
 '1901' '5407' '1954' '0108' '1964' '1905' '4401' '3004' '3307' '1990'
 '1936' '4300' '4008' '0104' '3490' '2600' '3002' '1926' '5405' '4120'
 '1972' '3028' '3011' '0711' '1961' '0715' '3500' '4905' '1008' '3318'
 '3906' '0706' '0709' '3315' '3611' '1967' '1602' '1509' '3401' '1951'
 '1515' '3501' '0106' '4115' '3902' '2100' '1800' '4311' '1013' '1931'
 '4902' '4106' '4114' '0790' '3712' '1934' '1001' '3905' '3051' '0300'
 '0708' '3335' '3014' '3103' '4111' '3200' '1400' '2400' '1507' '4214'
 '1600' '3920' '0112' '5004' '4305' '3618' '2109' '5200' '5406' '1947'
 '2712' '3621' '5007' '4117' '1928' '3781' '5102' '3101' '1989' '3496'
 '3705' '1944' '0601' '3308' '1012' '3345' '2303' '4103' '1952' '5501'
 '1923' '2110' '2002' '4802' '0791' '3040' '4901' '4980' '5300' '5606'
 '3013' '0500' '0200' '3314' '1801' '3048' '3341' '3104' '4104' '0600'
 '1918' '1913' '3316' '3608' '4304' '0702' '4102' '4004' '1921' '2601'
 '3305' '1506' '1970' '3392' '3021' '4212' '3325' '1959' '0707' '0404'
 '5201' '1939' '1968' '1510' '3617' '1301' '0102' '3901' '4201' '3601'
 '3630' '2108' '4906' '3337' '1960' '3904' '3780' '5701' '0103' '3606'
 '1304' '3049' '3631' '1702' '3306' '2102' '1201' '1917' '4315' '2405'
 '1601' '2704' '5202' '2301' '0705' '5603' '3029' '5403' '4205' '2404'
 '4501' '3106' '1945' '4005' '3317' '2200' '0792' '1927' '1935' '4306'
 '1004' '2111' '5605' '4202' '1504' '5801' '1910' '4709' '2001' '4101'
 '1015' '5009' '1974' '4127' '3707' '0902' '0501' '5602' '3708' '5006'
 '4805' '4002' '2709' '0901' '3704' '3690' '1501' '4708' '1929' '4804'
 '1505' '5408' '0602' '3336' '5003' '4404' '2104' '1007' '2105' '0714'
 '2804' '2902' '1690' '2705' '3402' '1511' '4108' '3680' '5005' '4801'
 '2711' '5101' '1999' '2801' '1101' '4206' '1102' '2701' '3005' '1011'
 '0801' '4001' '5404' '4118' '5702' '2803' '1971' '2710' '1991' '2707'
 '3613' '1701' '0703' '0302' '4107' '0301' '0304' '3201' '4701' '1508'
 '0110' '2103' '1924' '1980' '4003' '2304' '4580' '5802' '1305' '3303'
 '2703' '4706' '0305' '1930' '1302' '1306' '0716' '4119' '0401' '3023'
 '1002' '4105' '1958' '4112' '2403']


Уникальные значения параметра county_location


['los angeles' 'san mateo' 'san francisco' 'solano' 'riverside'
 'san diego' 'ventura' 'nevada' 'orange' 'santa clara' 'modoc'
 'contra costa' 'sacramento' 'san bernardino' 'el dorado' 'yuba'
 'del norte' 'marin' 'butte' 'yolo' 'humboldt' 'madera' 'imperial'
 'santa cruz' 'napa' 'fresno' 'sierra' 'mendocino' 'alameda' 'kern' 'lake'
 'stanislaus' 'placer' 'santa barbara' 'tuolumne' 'kings'
 'san luis obispo' 'amador' 'glenn' 'san joaquin' 'merced' 'sonoma'
 'shasta' 'monterey' 'siskiyou' 'sutter' 'tulare' 'inyo' 'mono'
 'san benito' 'lassen' 'plumas' 'tehama' 'colusa' 'trinity' 'calaveras'
 'alpine' 'mariposa']


Уникальные значения параметра distance


[ 500.   528.     0.  ... 1338.   183.2 1878. ]


Уникальные значения параметра direction


['east' 'south' None 'west' 'north']


Уникальные значения параметра intersection


[ 0.  1. nan]


Уникальные значения параметра weather


['clear' 'cloudy' 'snowing' 'raining' 'fog' None 'other' 'wind']


Уникальные значения параметра location_type


['highway' None 'ramp' 'intersection']


Уникальные значения параметра collision_damage


['scratch' 'small damage' 'middle damage' 'severe damage' 'fatal']


Уникальные значения параметра party_count


[ 3  1  2  4  5  6  7  8  9 10]


Уникальные значения параметра primary_collision_factor


['vehicle code violation' 'unknown' 'other improper driving'
 'other than driver' None 'fell asleep']


Уникальные значения параметра pcf_violation_category


['speeding' 'improper turning' 'automobile right of way'
 'unsafe starting or backing' 'improper passing' 'unknown'
 'unsafe lane change' 'dui' 'pedestrian violation'
 'other improper driving' 'traffic signals and signs' None
 'wrong side of road' 'other than driver (or pedestrian)'
 'following too closely' 'other hazardous violation' 'impeding traffic'
 'hazardous parking' 'pedestrian right of way' 'other equipment' 'brakes'
 'lights' 'fell asleep']


Уникальные значения параметра type_of_collision


['rear end' 'hit object' 'broadside' 'sideswipe' 'overturned' None
 'head-on' 'other']


Уникальные значения параметра motor_vehicle_involved_with


['other motor vehicle' 'other object' 'fixed object' 'non-collision'
 'parked motor vehicle' 'motor vehicle on other roadway' 'animal'
 'bicycle' None 'train']


Уникальные значения параметра road_surface


['dry' 'wet' 'snowy' None 'slippery']


Уникальные значения параметра road_condition


['normal' 'loose material' 'construction' 'other' 'reduced width'
 'obstruction' 'holes' None 'flooded']


Уникальные значения параметра lighting


['daylight' 'dark with no street lights' 'dark with street lights'
 'dusk or dawn' 'dark with street lights not functioning' None]


Уникальные значения параметра control_device


['none' 'functioning' None 'not functioning' 'obscured']


Уникальные значения параметра collision_date


[datetime.date(2012, 4, 14) datetime.date(2012, 4, 22)
 datetime.date(2012, 5, 6) datetime.date(2012, 6, 3)
 datetime.date(2012, 1, 12) datetime.date(2012, 1, 8)
 datetime.date(2012, 2, 7) datetime.date(2012, 1, 27)
 datetime.date(2012, 1, 22) datetime.date(2012, 1, 19)
 datetime.date(2012, 2, 20) datetime.date(2012, 2, 19)
 datetime.date(2012, 2, 18) datetime.date(2012, 2, 3)
 datetime.date(2012, 1, 10) datetime.date(2012, 3, 1)
 datetime.date(2012, 2, 22) datetime.date(2012, 3, 11)
 datetime.date(2012, 2, 29) datetime.date(2012, 2, 26)
 datetime.date(2012, 3, 3) datetime.date(2012, 3, 20)
 datetime.date(2012, 3, 17) datetime.date(2012, 3, 14)
 datetime.date(2012, 2, 4) datetime.date(2012, 4, 1)
 datetime.date(2012, 4, 5) datetime.date(2012, 4, 20)
 datetime.date(2012, 4, 13) datetime.date(2012, 4, 16)
 datetime.date(2012, 4, 7) datetime.date(2012, 4, 23)
 datetime.date(2012, 4, 17) datetime.date(2012, 3, 30)
 datetime.date(2012, 5, 3) datetime.date(2012, 5, 2)
 datetime.date(2012, 4, 21) datetime.date(2012, 5, 16)
 datetime.date(2012, 5, 8) datetime.date(2012, 5, 14)
 datetime.date(2012, 4, 30) datetime.date(2012, 5, 23)
 datetime.date(2012, 5, 15) datetime.date(2012, 5, 17)
 datetime.date(2012, 12, 7) datetime.date(2012, 5, 24)
 datetime.date(2012, 6, 4) datetime.date(2012, 5, 30)
 datetime.date(2012, 6, 1) datetime.date(2012, 3, 21)
 datetime.date(2012, 1, 16) datetime.date(2012, 5, 4)
 datetime.date(2012, 1, 4) datetime.date(2012, 1, 25)
 datetime.date(2012, 1, 23) datetime.date(2012, 2, 1)
 datetime.date(2012, 2, 8) datetime.date(2012, 2, 21)
 datetime.date(2012, 1, 30) datetime.date(2012, 1, 20)
 datetime.date(2012, 3, 19) datetime.date(2012, 3, 15)
 datetime.date(2012, 2, 17) datetime.date(2012, 3, 13)
 datetime.date(2012, 4, 3) datetime.date(2012, 3, 23)
 datetime.date(2012, 3, 28) datetime.date(2012, 3, 27)
 datetime.date(2012, 3, 2) datetime.date(2012, 3, 31)
 datetime.date(2012, 5, 1) datetime.date(2012, 4, 11)
 datetime.date(2012, 4, 28) datetime.date(2012, 4, 8)
 datetime.date(2012, 5, 10) datetime.date(2012, 5, 11)
 datetime.date(2012, 5, 19) datetime.date(2012, 5, 21)
 datetime.date(2012, 5, 27) datetime.date(2012, 5, 26)
 datetime.date(2012, 5, 25) datetime.date(2012, 4, 12)
 datetime.date(2012, 3, 4) datetime.date(2012, 2, 11)
 datetime.date(2012, 1, 15) datetime.date(2012, 1, 18)
 datetime.date(2012, 1, 11) datetime.date(2012, 1, 6)
 datetime.date(2012, 1, 13) datetime.date(2012, 2, 15)
 datetime.date(2012, 2, 5) datetime.date(2012, 1, 28)
 datetime.date(2012, 2, 14) datetime.date(2012, 2, 9)
 datetime.date(2012, 2, 13) datetime.date(2012, 3, 25)
 datetime.date(2012, 3, 18) datetime.date(2012, 3, 24)
 datetime.date(2012, 4, 10) datetime.date(2012, 4, 9)
 datetime.date(2012, 4, 19) datetime.date(2012, 4, 27)
 datetime.date(2012, 4, 15) datetime.date(2012, 4, 25)
 datetime.date(2012, 5, 13) datetime.date(2012, 5, 20)
 datetime.date(2012, 5, 29) datetime.date(2012, 6, 8)
 datetime.date(2012, 5, 18) datetime.date(2012, 5, 28)
 datetime.date(2012, 3, 12) datetime.date(2012, 2, 23)
 datetime.date(2012, 3, 22) datetime.date(2012, 4, 6)
 datetime.date(2012, 1, 14) datetime.date(2012, 3, 16)
 datetime.date(2012, 6, 11) datetime.date(2012, 1, 3)
 datetime.date(2012, 1, 2) datetime.date(2012, 1, 5)
 datetime.date(2012, 2, 2) datetime.date(2012, 1, 21)
 datetime.date(2012, 2, 16) datetime.date(2012, 3, 10)
 datetime.date(2012, 2, 27) datetime.date(2012, 2, 28)
 datetime.date(2012, 3, 6) datetime.date(2012, 3, 9)
 datetime.date(2012, 4, 26) datetime.date(2012, 5, 5)
 datetime.date(2012, 7, 3) datetime.date(2012, 11, 26)
 datetime.date(2012, 11, 28) datetime.date(2012, 4, 4)
 datetime.date(2012, 1, 7) datetime.date(2012, 1, 1)
 datetime.date(2012, 1, 29) datetime.date(2012, 2, 25)
 datetime.date(2012, 2, 6) datetime.date(2012, 8, 18)
 datetime.date(2012, 4, 29) datetime.date(2012, 5, 12)
 datetime.date(2012, 5, 9) datetime.date(2012, 2, 10)
 datetime.date(2012, 1, 24) datetime.date(2012, 1, 9)
 datetime.date(2012, 2, 12) datetime.date(2012, 4, 18)
 datetime.date(2012, 6, 2) datetime.date(2012, 7, 15)
 datetime.date(2012, 8, 22) datetime.date(2012, 10, 28)
 datetime.date(2012, 11, 23) datetime.date(2012, 6, 9)
 datetime.date(2012, 1, 17) datetime.date(2012, 1, 26)
 datetime.date(2012, 2, 24) datetime.date(2012, 8, 26)
 datetime.date(2012, 4, 2) datetime.date(2012, 3, 8)
 datetime.date(2012, 3, 7) datetime.date(2012, 8, 23)
 datetime.date(2012, 5, 31) datetime.date(2012, 3, 5)
 datetime.date(2012, 3, 26) datetime.date(2012, 5, 7)
 datetime.date(2012, 5, 22) datetime.date(2012, 6, 6)
 datetime.date(2012, 1, 31) datetime.date(2012, 3, 29)
 datetime.date(2012, 10, 18) datetime.date(2012, 4, 24)
 datetime.date(2012, 10, 15) datetime.date(2012, 7, 20)
 datetime.date(2012, 7, 28) datetime.date(2012, 9, 8)
 datetime.date(2012, 7, 8) datetime.date(2012, 6, 13)
 datetime.date(2012, 8, 21) datetime.date(2012, 7, 30)
 datetime.date(2012, 6, 7) datetime.date(2012, 6, 30)
 datetime.date(2012, 6, 14) datetime.date(2012, 6, 27)
 datetime.date(2012, 6, 5) datetime.date(2012, 8, 15)
 datetime.date(2012, 10, 3) datetime.date(2012, 9, 16)
 datetime.date(2012, 8, 3) datetime.date(2012, 7, 29)
 datetime.date(2012, 9, 11) datetime.date(2012, 9, 3)
 datetime.date(2012, 9, 17) datetime.date(2012, 12, 12)
 datetime.date(2012, 8, 14) datetime.date(2012, 9, 2)
 datetime.date(2012, 11, 5) datetime.date(2012, 11, 29)
 datetime.date(2012, 7, 10) datetime.date(2012, 9, 27)
 datetime.date(2012, 11, 25) datetime.date(2012, 8, 20)
 datetime.date(2012, 6, 16) datetime.date(2012, 9, 13)
 datetime.date(2012, 11, 18) datetime.date(2012, 9, 7)
 datetime.date(2012, 6, 12) datetime.date(2012, 9, 29)
 datetime.date(2012, 11, 17) datetime.date(2012, 6, 10)
 datetime.date(2012, 11, 13) datetime.date(2012, 12, 29)
 datetime.date(2012, 9, 14) datetime.date(2012, 8, 11)
 datetime.date(2012, 8, 25) datetime.date(2012, 9, 25)
 datetime.date(2012, 12, 18) datetime.date(2012, 11, 7)
 datetime.date(2012, 6, 29) datetime.date(2012, 11, 11)
 datetime.date(2012, 8, 30) datetime.date(2012, 12, 9)
 datetime.date(2012, 10, 7) datetime.date(2012, 11, 30)
 datetime.date(2012, 10, 21) datetime.date(2012, 8, 5)
 datetime.date(2012, 10, 14) datetime.date(2012, 8, 8)
 datetime.date(2012, 10, 31) datetime.date(2012, 8, 4)
 datetime.date(2012, 8, 9) datetime.date(2012, 9, 15)
 datetime.date(2012, 9, 9) datetime.date(2012, 7, 16)
 datetime.date(2012, 10, 12) datetime.date(2012, 12, 1)
 datetime.date(2012, 7, 31) datetime.date(2012, 12, 10)
 datetime.date(2012, 11, 2) datetime.date(2012, 10, 23)
 datetime.date(2012, 9, 10) datetime.date(2012, 9, 12)
 datetime.date(2012, 6, 26) datetime.date(2012, 10, 19)
 datetime.date(2012, 10, 11) datetime.date(2012, 10, 29)
 datetime.date(2012, 7, 7) datetime.date(2012, 7, 5)
 datetime.date(2012, 11, 3) datetime.date(2012, 10, 8)
 datetime.date(2012, 11, 14) datetime.date(2012, 6, 17)
 datetime.date(2012, 8, 13) datetime.date(2012, 12, 21)
 datetime.date(2012, 8, 1) datetime.date(2012, 10, 26)
 datetime.date(2012, 12, 3) datetime.date(2012, 9, 4)
 datetime.date(2012, 6, 22) datetime.date(2012, 8, 17)
 datetime.date(2012, 11, 4) datetime.date(2012, 8, 16)
 datetime.date(2012, 7, 11) datetime.date(2012, 8, 7)
 datetime.date(2012, 9, 30) datetime.date(2012, 11, 10)
 datetime.date(2012, 9, 23) datetime.date(2012, 8, 12)
 datetime.date(2012, 10, 25) datetime.date(2012, 8, 6)
 datetime.date(2012, 12, 23) datetime.date(2012, 7, 9)
 datetime.date(2012, 7, 2) datetime.date(2012, 8, 28)
 datetime.date(2012, 10, 20) datetime.date(2012, 7, 19)
 datetime.date(2012, 12, 26) datetime.date(2012, 8, 31)
 datetime.date(2012, 12, 19) datetime.date(2012, 11, 8)
 datetime.date(2012, 12, 28) datetime.date(2012, 10, 1)
 datetime.date(2012, 7, 25) datetime.date(2012, 9, 22)
 datetime.date(2012, 10, 4) datetime.date(2012, 10, 6)
 datetime.date(2012, 9, 6) datetime.date(2012, 8, 24)
 datetime.date(2012, 9, 20) datetime.date(2012, 12, 15)
 datetime.date(2012, 10, 10) datetime.date(2012, 12, 27)
 datetime.date(2012, 7, 14) datetime.date(2012, 7, 17)
 datetime.date(2012, 6, 24) datetime.date(2012, 8, 27)
 datetime.date(2012, 12, 6) datetime.date(2012, 7, 22)
 datetime.date(2012, 12, 24) datetime.date(2012, 6, 15)
 datetime.date(2012, 11, 24) datetime.date(2012, 11, 19)
 datetime.date(2012, 8, 29) datetime.date(2012, 10, 22)
 datetime.date(2012, 9, 24) datetime.date(2012, 11, 21)
 datetime.date(2012, 11, 12) datetime.date(2012, 9, 19)
 datetime.date(2012, 8, 2) datetime.date(2012, 9, 18)
 datetime.date(2012, 12, 31) datetime.date(2012, 12, 11)
 datetime.date(2012, 10, 27) datetime.date(2012, 12, 22)
 datetime.date(2012, 6, 18) datetime.date(2012, 10, 13)
 datetime.date(2012, 11, 9) datetime.date(2012, 12, 5)
 datetime.date(2012, 7, 27) datetime.date(2012, 7, 18)
 datetime.date(2012, 6, 28) datetime.date(2012, 10, 5)
 datetime.date(2012, 6, 23) datetime.date(2012, 12, 13)
 datetime.date(2012, 12, 2) datetime.date(2012, 7, 12)
 datetime.date(2012, 11, 27) datetime.date(2012, 6, 19)
 datetime.date(2012, 6, 25) datetime.date(2012, 11, 1)
 datetime.date(2012, 12, 20) datetime.date(2012, 7, 1)
 datetime.date(2012, 9, 26) datetime.date(2012, 10, 2)
 datetime.date(2012, 9, 1) datetime.date(2012, 10, 16)
 datetime.date(2012, 9, 5) datetime.date(2012, 9, 28)
 datetime.date(2012, 12, 17) datetime.date(2012, 7, 21)
 datetime.date(2012, 10, 24) datetime.date(2012, 8, 10)
 datetime.date(2012, 12, 14) datetime.date(2012, 11, 22)
 datetime.date(2012, 9, 21) datetime.date(2012, 7, 24)
 datetime.date(2012, 11, 20) datetime.date(2012, 8, 19)
 datetime.date(2012, 7, 23) datetime.date(2012, 7, 4)
 datetime.date(2012, 12, 8) datetime.date(2012, 6, 20)
 datetime.date(2012, 11, 6) datetime.date(2012, 10, 17)
 datetime.date(2012, 7, 26) datetime.date(2012, 12, 16)
 datetime.date(2012, 11, 16) datetime.date(2012, 12, 25)
 datetime.date(2012, 10, 30) datetime.date(2012, 10, 9)
 datetime.date(2012, 7, 13) datetime.date(2012, 6, 21)
 datetime.date(2012, 7, 6) datetime.date(2012, 11, 15)
 datetime.date(2012, 12, 4) datetime.date(2012, 12, 30)]


Уникальные значения параметра collision_time


[datetime.time(13, 15) datetime.time(1, 50) datetime.time(0, 5) ...
 datetime.time(4, 23) datetime.time(4, 34) datetime.time(4, 52)]


Уникальные значения параметра collision_hour


[13.  1.  0. 15. 10. 18. 16. 11. 20. 21. 23. 17.  8.  7. 12. 22.  9. 14.
  6.  2.  3.  4. 19.  5. nan]


Уникальные значения параметра collision_month


[ 4.  5.  6.  1.  2.  3. 12.  7. 11.  8. 10.  9.]


df['vehicle_age'] =df['vehicle_age'].fillna( df.groupby('vehicle_type')['vehicle_age'].transform('median'))


df = df.loc[df['vehicle_age']<=10]


df['weather'] = df['weather'].fillna('other')


df['road_condition'] = df['road_condition'].fillna('normal')


df['primary_collision_factor'] = df['primary_collision_factor'].fillna('unknown')


df['pcf_violation_category'] = df['pcf_violation_category'].fillna('unknown')


df['direction'] = df['direction'].fillna('unknown')


df['type_of_collision'] = df['type_of_collision'].fillna('other')


df['party_sobriety'] = df['party_sobriety'].fillna(df['party_drug_physical'])


df['party_sobriety'] = df['party_sobriety'].fillna('unknown')


df['location_type'] = df['location_type'].fillna(df['intersection'])


df['control_device'] = df['control_device'].fillna('unknown')
df['cellphone_in_use'] = df['cellphone_in_use'].fillna('unknown')


df['cellphone_in_use'] = df['cellphone_in_use'].replace(0,'no')
df['cellphone_in_use'] = df['cellphone_in_use'].replace(1,'yes')


df['insurance_premium'] = df['insurance_premium'].fillna( df.groupby(['vehicle_type','vehicle_age'])['insurance_premium'].transform('median'))


df = df.loc[df['collision_time'].notna()]


df['lighting'] = df.groupby(['weather','collision_time', 'collision_date', 
                'county_city_location'])['lighting'].transform(lambda x: x.fillna((x.mode()[0] if x.count()!=0 else 'unknown')))


df['road_surface'] = df.groupby(['weather','collision_time', 'collision_date', 
                'county_city_location','road_condition'])['road_surface'].transform(lambda x: x.fillna((x.mode()[0] if x.count()!=0 else 'unknown')))


df['control_device'] = df['control_device'].replace('none','no')


df['road_surface'] = df['road_surface'].fillna('unknown')
df['lighting'] = df['lighting'].fillna('unknown')


if df.duplicated().sum()>0:
    df.drop_duplicates(inplace=True, ignore_index=True)


final_len_df = len(df)


print(f'Удалено {100-final_len_df*100/begin_len_df:.2f}% строк')

Удалено 5.92% строк


columns = ['at_fault', 'vehicle_age', 'party_sobriety', 'cellphone_in_use', 
           'county_city_location', 'weather', 'road_surface', 'road_condition', 'lighting', 'control_device',
           'direction', 'insurance_premium'] #, 'insurance_premium'


df = df[columns]


df.isna().sum()

at_fault                0
vehicle_age             0
party_sobriety          0
cellphone_in_use        0
county_city_location    0
weather                 0
road_surface            0
road_condition          0
lighting                0
control_device          0
direction               0
insurance_premium       0
dtype: int64


df.to_csv('df_for_model.csv')


sns.countplot(x=df['at_fault'])

<Axes: xlabel='at_fault', ylabel='count'>


import phik
from phik.report import plot_correlation_matrix
phik_overview = df.phik_matrix(interval_cols=['vehicle_age', 'insurance_premium']) #'insurance_premium', 
phik_overview.round(2)

plot_correlation_matrix(phik_overview.values, 
                        x_labels=phik_overview.columns, 
                        y_labels=phik_overview.index, 
                        vmin=0, vmax=1, color_map="YlGn", 
                        title=r"Корреляция $\phi_K$", 
                        fontsize_factor=1, 
                        figsize=(8,7))
plt.tight_layout()
plt.show()


plt.figure(figsize=(2,5))
sns.heatmap(phik_overview.sort_values(by = 'at_fault', ascending=False)[['at_fault']], cmap='YlGn', annot=True, annot_kws={'size':11}, fmt='.2g')
plt.title('Корреляция по таргету', fontsize=13)
plt.show()


columns_numeric = ['insurance_premium', 'vehicle_age']
columns_cat = ['party_sobriety', 'cellphone_in_use', 
           'county_city_location', 'weather', 'road_surface', 'road_condition', 'lighting', 'control_device',
           'direction']


df = df.astype({
    **{_:'int' for _ in columns_numeric},
    **{_:'category' for _ in columns_cat}
})


df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 97152 entries, 0 to 97151
Data columns (total 12 columns):
 #   Column                Non-Null Count  Dtype   
---  ------                --------------  -----   
 0   at_fault              97152 non-null  int64   
 1   vehicle_age           97152 non-null  int32   
 2   party_sobriety        97152 non-null  category
 3   cellphone_in_use      97152 non-null  category
 4   county_city_location  97152 non-null  category
 5   weather               97152 non-null  category
 6   road_surface          97152 non-null  category
 7   road_condition        97152 non-null  category
 8   lighting              97152 non-null  category
 9   control_device        97152 non-null  category
 10  direction             97152 non-null  category
 11  insurance_premium     97152 non-null  int32   
dtypes: category(9), int32(2), int64(1)
memory usage: 2.4 MB


X_train, X_test, y_train, y_test = train_test_split(df.drop('at_fault', axis=1),
                                                    df['at_fault'],
                                                    test_size=0.3,
                                                    shuffle=True,
                                                    random_state=RANDOM_STATE)


print(X_train.shape, y_train.shape, X_test.shape, y_test.shape)

(68006, 11) (68006,) (29146, 11) (29146,)


sns.countplot(x=y_test)

<Axes: xlabel='at_fault', ylabel='count'>


X_train_ohe = X_train.copy()
X_test_ohe = X_test.copy()


ohe = OneHotEncoder(sparse=False, handle_unknown = 'ignore')

train = ohe.fit_transform(X_train_ohe[columns_cat])

# Из-за разницы версий библиотек метод взятия названий может именоваться иначе
train = pd.DataFrame(train, columns=ohe.get_feature_names_out(columns_cat), index=X_train_ohe.index)

X_train_ohe = train.join(X_train_ohe[columns_numeric])


test = ohe.transform(X_test_ohe[columns_cat])

# Из-за разницы версий библиотек метод взятия названий может именоваться иначе 
test = pd.DataFrame(test, columns=ohe.get_feature_names_out(columns_cat), index=X_test_ohe.index)

X_test_ohe = test.join(X_test_ohe[columns_numeric])


scaler = StandardScaler().fit(X_train[columns_numeric])

X_train_ohe[columns_numeric] = scaler.transform(X_train_ohe[columns_numeric])
X_test_ohe[columns_numeric] = scaler.transform(X_test_ohe[columns_numeric])
                             
X_train[columns_numeric] = scaler.transform(X_train[columns_numeric])
X_test[columns_numeric] = scaler.transform(X_test[columns_numeric])


rating_table = pd.DataFrame(columns=['f1', 'LearningTime', 'PredictingTime'])
rating_table


def add_line_df_score(df_score, model, mean_score, time, predicted_time):
    
    df_score.loc[model, 'f1'] = "{:.2%}".format(mean_score)
    df_score.loc[model, 'LearningTime'] = time
    df_score.loc[model, 'PredictingTime'] = predicted_time
    
    return df_score


start = time.time()

parameters = {'max_depth' : [0, 160], 'min_samples_split' : [1, 200], 'min_samples_leaf' : [0, 30]}

grid_tree = GridSearchCV(DecisionTreeClassifier(random_state=RANDOM_STATE),
                         parameters, scoring="f1", cv = 5, n_jobs= -1, return_train_score=True)
grid_tree.fit(X_train_ohe, y_train)

print('Best params:', grid_tree.best_params_)
print('Best score:', grid_tree.best_score_)
time_tree = time.time() - start
print('time =', round(time_tree, 2), 'c')

Best params: {'max_depth': 160, 'min_samples_leaf': 30, 'min_samples_split': 200}
Best score: 0.5335023598353046
time = 6.4 c


start = time.time()
model_tree = DecisionTreeClassifier(max_depth=grid_tree.best_params_.get('max_depth'),
                                    min_samples_leaf=grid_tree.best_params_.get('min_samples_leaf'),
                                    min_samples_split= grid_tree.best_params_.get('min_samples_split'))
#model_tree = grid_tree.fit(X_train_ohe, y_train,fit_params=grid_tree.best_params_)

model_tree.fit(X_train_ohe, y_train)
time_tree = time.time() - start
print('time =', round(time_tree, 2), 'c')

time = 1.82 c


start = time.time()
predicted_tree = model_tree.predict(X_train_ohe)

predicted_time_tree = time.time() - start


add_line_df_score(rating_table, 'DecisionTreeClassifier', grid_tree.best_score_, time_tree, predicted_time_tree)


start = time.time()

parameters_forest = {'n_estimators': [1, 200], 'max_depth' : [1, 300]}

grid_forest = GridSearchCV(RandomForestClassifier(random_state=RANDOM_STATE),
                        parameters_forest, scoring="f1", cv=5, n_jobs=-1, return_train_score=True)
grid_forest.fit(X_train_ohe, y_train)



print('Best params:', grid_forest.best_params_)
print('Best score:', grid_forest.best_score_)
time_forest = time.time() - start
print('time =', round(time_forest), 'c')

Best params: {'max_depth': 300, 'n_estimators': 200}
Best score: 0.5312613345616243
time = 199 c


start = time.time()
model_forest = RandomForestClassifier(max_depth=grid_forest.best_params_.get('max_depth'),
                                      n_estimators=grid_forest.best_params_.get('n_estimators'))

model_forest.fit(X_train_ohe, y_train)
time_tree = time.time() - start
print('time =', round(time_forest, 2), 'c')

time = 198.69 c


start = time.time()
predicted_forest = model_forest.predict(X_train_ohe)

predicted_time_forest = time.time() - start


add_line_df_score(rating_table, 'RandomForestClassifier', grid_forest.best_score_, time_forest, predicted_time_forest)


start = time.time()
from sklearn.linear_model import LogisticRegression
parameters = {'solver' : ['liblinear'], 'C' : [0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1, 1.1], 'penalty': ['l1', 'l2']}

grid_lr = GridSearchCV(LogisticRegression(random_state=RANDOM_STATE),
                         parameters, scoring="f1", cv = 5, n_jobs= -1, return_train_score=True)
grid_lr.fit(X_train_ohe, y_train)

print('Best params:', grid_lr.best_params_)
print('Best score:', grid_lr.best_score_)
time_lr = time.time() - start
print('time =', round(time_lr, 2), 'c')

Best params: {'C': 1, 'penalty': 'l2', 'solver': 'liblinear'}
Best score: 0.5076897429358324
time = 111.59 c


start = time.time()
model_lr = LogisticRegression(C=grid_lr.best_params_.get('C'),
                                    penalty=grid_lr.best_params_.get('penalty'),
                                    solver= grid_lr.best_params_.get('solver'))

model_lr.fit(X_train_ohe, y_train)
time_lr = time.time() - start
print('time =', round(time_lr, 2), 'c')

time = 0.55 c


start = time.time()
predicted_lr = model_lr.predict(X_train_ohe)

predicted_time_lr = time.time() - start


add_line_df_score(rating_table, 'LogisticRegression', grid_lr.best_score_, time_lr, predicted_time_lr)


start = time.time()

parameters_cat = {'n_estimators': [1, 200], 'max_depth' : [1, 300], 'l2_leaf_reg': [1, 50]}

grid_cat = GridSearchCV(CatBoostClassifier(random_state=RANDOM_STATE),
                        parameters_cat, scoring="f1",
                        cv=5, n_jobs=-1, return_train_score=True)

grid_cat.fit(X_train, y_train, silent=True, cat_features=columns_cat)

time_cat = time.time() - start

print('Best params:', grid_cat.best_params_)
print('Best score:', grid_cat.best_score_)
print('time =', round(time_cat,2), 'c')

Best params: {'l2_leaf_reg': 50, 'max_depth': 1, 'n_estimators': 200}
Best score: 0.5067059062309234
time = 14.43 c


start = time.time()

model_cat = CatBoostClassifier(n_estimators=grid_cat.best_params_.get('n_estimators'),\
                              max_depth=grid_cat.best_params_.get('max_depth'),\
                              l2_leaf_reg=grid_cat.best_params_.get('l2_leaf_reg'),\
                              random_state=RANDOM_STATE, silent=True,\
                              cat_features=columns_cat)


model_cat.fit(X_train, y_train, cat_features=columns_cat)

time_cat = time.time() - start
print('time =', time_cat)

time = 2.074017286300659


start = time.time()
predicted_cat = model_cat.predict(X_train)

predicted_time_cat = time.time() - start
print('time =', round(predicted_time_cat, 2), 'c')

time = 0.03 c


add_line_df_score(rating_table, 'CatBoostClassifier', grid_cat.best_score_, time_cat, predicted_time_cat)


start = time.time()


parameters_lgbm = {
    'max_depth': range(4,10),
    'n_estimators': range(1,100)
}
lgbm = LGBMClassifier(seed=RANDOM_STATE, verbose = -1)

grid_lgbm = GridSearchCV(lgbm, parameters_lgbm, cv=5, scoring='f1', return_train_score=True)
grid_lgbm.fit(X_train, y_train, categorical_feature=columns_cat);
grid_lgbm.best_params_
time_lgbm = time.time() - start
print('Best params:', grid_lgbm.best_params_)
print('Best score:', grid_lgbm.best_score_)
print('time =', round(time_lgbm), 'c')

Best params: {'max_depth': 9, 'n_estimators': 92}
Best score: 0.5367580470954556
time = 443 c


start = time.time()

model_lgbm = LGBMClassifier(max_depth=grid_lgbm.best_params_.get('max_depth'),
                           n_estimators=grid_lgbm.best_params_.get('n_estimators'),
                           seed=RANDOM_STATE, verbose = -1)
                                                      
model_lgbm.fit(X_train, y_train, categorical_feature=columns_cat)
time_lgbm = time.time() - start
print('time =', round(time_lgbm), 'c')

time = 0 c


start = time.time()
predicted_lgbm = model_lgbm.predict(X_train)

predicted_time_lgbm = time.time() - start


add_line_df_score(rating_table, 'LGBMClassifier', grid_lgbm.best_score_, time_lgbm, predicted_time_lgbm)


rating_table.sort_values('f1', ascending=False).style.highlight_max(subset=['f1'])


best_model = model_lgbm
predict_best = best_model.predict(X_test)
print(f'Метрика Recall на тестовых данных = {"{:.2%}".format(recall_score(y_test, predict_best))}')
print(f'Метрика Precision на тестовых данных = {"{:.2%}".format(precision_score(y_test, predict_best))}')
print(f'Метрика F1 на тестовых данных = {"{:.2%}".format(f1_score(y_test, predict_best))}')

Метрика Recall на тестовых данных = 45.19%
Метрика Precision на тестовых данных = 68.23%
Метрика F1 на тестовых данных = 54.37%


cm = confusion_matrix(y_test, predict_best, labels=best_model.classes_)
from sklearn.metrics import precision_recall_curve, PrecisionRecallDisplay
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 5))
cmd = ConfusionMatrixDisplay(cm, display_labels=best_model.classes_)
cmd.plot(ax=ax1, cmap='YlGn', values_format='g')
ax1.set_title('Матрица ошибок')
prec, recall, _ = precision_recall_curve(y_test, predict_best)
PrecisionRecallDisplay(prec, recall).plot(ax=ax2)
ax2.set_title('График полнота-точность')
plt.show()


explainer = shap.TreeExplainer(best_model)
shap_values = explainer.shap_values(X_train)

shap.summary_plot(shap_values, X_train)


feature_names = X_train.columns
model_importances = pd.Series(best_model.feature_importances_, index=feature_names).reset_index()
model_importances.index=model_importances['index']
model_importances.rename(columns={0:'features'}, inplace=True)
plt.figure(figsize=(2,8))
sns.heatmap(model_importances[['features']].sort_values(by='features',ascending=False),\
            annot=True, cmap='Greens_r')
plt.title('Correlation Heatmap'+'\n',fontsize=16)

Text(0.5, 1.0, 'Correlation Heatmap\n')


fig = px.histogram(
    df, x='vehicle_age', color='at_fault',
    barmode='group',
    title='Зависимость виновности в ДТП от возраста автомобиля',
    histfunc='count'
)
fig.show()


df.columns

Index(['at_fault', 'vehicle_age', 'party_sobriety', 'cellphone_in_use',
       'county_city_location', 'weather', 'road_surface', 'road_condition',
       'lighting', 'control_device', 'direction', 'insurance_premium'],
      dtype='object')


fig = px.histogram(
    df, x='insurance_premium', color='at_fault',
    barmode='group',
    title='Зависимость виновности в ДТП от страховки',
    histfunc='count'
)
fig.show()

	case_id	db_year
0	0081715	2021
1	0726202	2021
2	3858022	2021
3	3899441	2021
4	3899442	2021
5	3899445	2021
6	3899446	2021
7	3899449	2021
8	3899450	2021
9	3899453	2021

	total_ids	month	year	month_only
36	32020	2012-01-01	2012.0	1.0
37	30377	2012-02-01	2012.0	2.0
38	32498	2012-03-01	2012.0	3.0
39	29143	2012-04-01	2012.0	4.0
40	25168	2012-05-01	2012.0	5.0
41	3062	2012-06-01	2012.0	6.0
42	390	2012-07-01	2012.0	7.0
43	622	2012-08-01	2012.0	8.0
44	510	2012-09-01	2012.0	9.0
45	304	2012-10-01	2012.0	10.0
46	253	2012-11-01	2012.0	11.0
47	200	2012-12-01	2012.0	12.0
48	144	2013-01-01	2013.0	1.0
49	108	2013-02-01	2013.0	2.0
50	46	2013-03-01	2013.0	3.0
51	5	2013-04-01	2013.0	4.0
52	1	2013-06-01	2013.0	6.0
53	1	2013-07-01	2013.0	7.0
54	1	2020-03-01	2020.0	3.0
55	1	2020-07-01	2020.0	7.0

	count	mean	std	min	25%	50%	75%	max
party_number	103267.0	1.599369	0.678538	1.0	1.0	2.0	2.0	9.0
vehicle_age	100606.0	5.217840	3.125412	0.0	3.0	5.0	7.0	161.0
at_fault	103267.0	0.446706	0.497154	0.0	0.0	0.0	1.0	1.0
insurance_premium	102471.0	38.699320	16.286041	0.0	25.0	36.0	50.0	105.0
cellphone_in_use	93264.0	0.020533	0.141816	0.0	0.0	0.0	0.0	1.0
distance	103267.0	696.619596	5522.375964	0.0	0.0	100.0	500.0	1584000.0
intersection	102918.0	0.254717	0.435704	0.0	0.0	0.0	1.0	1.0
party_count	103267.0	2.152004	0.794783	1.0	2.0	2.0	2.0	10.0
collision_time_2	103157.0	13.331873	5.264992	0.0	10.0	14.0	17.0	23.0
collision_date_2	103267.0	3.110965	1.671313	1.0	2.0	3.0	4.0	12.0

	f1	LearningTime	PredictingTime
DecisionTreeClassifier	53.35%	1.824887	0.09902
RandomForestClassifier	53.13%	198.689501	6.392586

	f1	LearningTime	PredictingTime
DecisionTreeClassifier	53.35%	1.824887	0.09902
RandomForestClassifier	53.13%	198.689501	6.392586
LogisticRegression	50.77%	0.554329	0.070227

Requirements¶

Описание проекта¶

Подключитесь к базе. Загрузите таблицы sql¶

Проведите первичное исследование таблиц¶

Вывод.¶

Проведите статистический анализ факторов ДТП¶

Статистика дтп по месяцам¶

Вывод¶

Дополнительные аналитические задачи:¶

Аварийность по регионам¶

Состояние водителя и степень повреждения.¶

В каких районах больше пьяных водителей.¶

Влияет ли направление движения на виновность?¶

Анализ серьёзности повреждений ТС от коробки передач.¶

Вывод¶

Создайте модель для оценки водительского риска¶

Проведём EDA¶

Перед заполнением пропусков посмотрим на данные, выбросы, ошибки.¶

Заполним пропуски¶

Формирование таблицы для обучения¶

Подготовка данных для моделирования¶

Корреляции.¶

Таргет и фичи¶

Трансформеры¶

Моделирование¶

DecisionTreeClassifier¶

RandomForestClassifier¶

LogisticRegression¶

CatBoostClassifier¶

LGBMClassifier¶

Проведите анализ важности факторов ДТП¶

Проанализируйте важность основных факторов, влияющих на вероятность ДТП¶

Для одного из выявленных важных факторов проведите дополнительное исследование¶

Выводы¶

	table_name	column_name	data_type
0	case_ids	case_id	text
1	case_ids	db_year	text
2	collisions	case_id	text
3	collisions	county_city_location	text
4	collisions	county_location	text
5	collisions	distance	real
6	collisions	direction	text
7	collisions	intersection	integer
8	collisions	weather_1	text
9	collisions	location_type	text
10	collisions	collision_damage	text
11	collisions	party_count	integer
12	collisions	primary_collision_factor	text
13	collisions	pcf_violation_category	text
14	collisions	type_of_collision	text
15	collisions	motor_vehicle_involved_with	text
16	collisions	road_surface	text
17	collisions	road_condition_1	text
18	collisions	lighting	text
19	collisions	control_device	text
20	collisions	collision_date	date
21	collisions	collision_time	time without time zone
22	parties	id	integer
23	parties	case_id	text
24	parties	party_number	integer
25	parties	party_type	text
26	parties	at_fault	integer
27	parties	insurance_premium	integer
28	parties	party_sobriety	text
29	parties	party_drug_physical	text
30	parties	cellphone_in_use	integer
31	vehicles	id	integer
32	vehicles	case_id	text
33	vehicles	party_number	integer
34	vehicles	vehicle_type	text
35	vehicles	vehicle_transmission	text
36	vehicles	vehicle_age	integer

	case_id	county_city_location	county_location	distance	direction	intersection	weather_1	location_type	collision_damage	party_count	primary_collision_factor	pcf_violation_category	type_of_collision	motor_vehicle_involved_with	road_surface	road_condition_1	lighting	control_device	collision_date	collision_time
0	4083072	1942	los angeles	528.0	north	0	cloudy	highway	small damage	2	vehicle code violation	unsafe lane change	sideswipe	other motor vehicle	wet	normal	daylight	none	2009-01-22	07:25:00
1	4083075	4313	santa clara	0.0	None	1	clear	None	small damage	1	vehicle code violation	improper passing	hit object	fixed object	dry	normal	dark with street lights	functioning	2009-01-03	02:26:00
2	4083073	0109	alameda	0.0	None	1	clear	None	scratch	2	vehicle code violation	improper turning	broadside	other motor vehicle	dry	normal	dark with street lights	functioning	2009-01-11	03:32:00
3	4083077	0109	alameda	0.0	None	1	clear	None	scratch	2	vehicle code violation	automobile right of way	broadside	other motor vehicle	dry	normal	daylight	functioning	2009-01-11	10:35:00
4	4083087	4313	santa clara	0.0	None	1	clear	None	scratch	2	vehicle code violation	speeding	rear end	other motor vehicle	dry	None	dark with street lights	functioning	2009-01-02	22:43:00
5	4083097	0109	alameda	0.0	None	1	clear	ramp	small damage	2	vehicle code violation	speeding	rear end	other motor vehicle	dry	normal	dark with street lights	functioning	2009-01-18	02:18:00
6	4083092	1942	los angeles	1320.0	south	0	clear	highway	scratch	2	vehicle code violation	unsafe lane change	rear end	other motor vehicle	dry	normal	daylight	none	2009-01-21	10:30:00
7	4083096	1942	los angeles	200.0	south	0	clear	highway	scratch	3	vehicle code violation	speeding	rear end	other motor vehicle	dry	normal	daylight	none	2009-01-21	08:55:00
8	4083100	1942	los angeles	2112.0	north	0	cloudy	highway	scratch	2	vehicle code violation	speeding	rear end	other motor vehicle	dry	normal	daylight	none	2009-01-21	16:20:00
9	4083103	4313	santa clara	0.0	None	1	cloudy	None	scratch	2	vehicle code violation	None	broadside	other motor vehicle	wet	normal	dusk or dawn	functioning	2009-01-02	15:55:00

	id	case_id	party_number	party_type	at_fault	insurance_premium	party_sobriety	party_drug_physical	cellphone_in_use
0	22	3899454	1	road signs	1	29.0	had not been drinking	None	0.0
1	23	3899454	2	road signs	0	7.0	had not been drinking	None	0.0
2	29	3899462	2	car	0	21.0	had not been drinking	None	0.0
3	31	3899465	2	road signs	0	24.0	had not been drinking	None	0.0
4	41	3899478	2	road bumper	0	NaN	not applicable	not applicable	0.0
5	43	3899481	2	road bumper	0	NaN	not applicable	not applicable	0.0
6	72	3899505	2	road bumper	0	NaN	not applicable	not applicable	0.0
7	73	3899505	3	road bumper	0	NaN	not applicable	not applicable	0.0
8	76	3899509	1	road bumper	0	NaN	had not been drinking	None	0.0
9	93	3981292	2	road bumper	0	NaN	had not been drinking	None	NaN

	id	case_id	party_number	vehicle_type	vehicle_transmission	vehicle_age
0	1175713	5305032	2	sedan	manual	3
1	1	3858022	1	sedan	auto	3
2	1175712	5305030	1	sedan	auto	3
3	1175717	5305033	3	sedan	auto	5
4	1175722	5305034	2	sedan	auto	5
5	1175737	5305046	2	sedan	auto	5
6	1175750	5305053	3	sedan	auto	9
7	1175715	5305033	1	sedan	manual	10
8	1175716	5305033	2	sedan	manual	4
9	1175758	5305060	2	sedan	auto	11

	constraint_name	table_name	column_name
0	case_ids_pk	case_ids	case_id
1	parties_pk	parties	id
2	vehicles_pk	vehicles	id
3	collisions_case_ids_case_id_fk	collisions	case_id
4	parties_case_ids_case_id_fk	parties	case_id
5	vehicles_case_ids_case_id_fk	vehicles	case_id

	county_location	count
22	santa cruz	11570
56	alpine	290
34	nevada	4121
35	imperial	3955
20	solano	14412
6	sacramento	56988
40	yuba	2327
53	mariposa	810

	county_location	pcf_violation_category	count
0	alameda	speeding	20721
1	alameda	improper turning	13408
2	alameda	unsafe lane change	5490
3	los angeles	speeding	135566
4	los angeles	improper turning	58083
5	los angeles	automobile right of way	57609
6	orange	speeding	42328
7	orange	improper turning	16047
8	orange	automobile right of way	13276
9	riverside	speeding	22392
10	riverside	improper turning	13402
11	riverside	automobile right of way	6798
12	sacramento	speeding	20381
13	sacramento	improper turning	9011
14	sacramento	automobile right of way	6386
15	san bernardino	speeding	24488
16	san bernardino	improper turning	14538
17	san bernardino	automobile right of way	9898
18	san diego	speeding	24890
19	san diego	improper turning	15368
20	san diego	automobile right of way	7379
21	santa clara	speeding	20745
22	santa clara	improper turning	7188
23	santa clara	unsafe lane change	4084

	collision_damage	party_drug_physical
1801092	small damage	None
1147588	small damage	None
1849654	small damage	None
539362	small damage	None

	collision_damage	party_drug_physical
158626	middle damage	None
206352	middle damage	None
69673	middle damage	None
150038	severe damage	None

	county_location	count
0	los angeles	40725
1	orange	13107
2	san diego	10870
3	san bernardino	8585
4	riverside	8182
5	alameda	5716
6	sacramento	5544
7	santa clara	4874

	sum	direction
0	81360	west
1	97140	south
2	97046	north
3	81523	east

	98655	6860	1147	41750	61109	86850	90839	89278
vehicle_type	coupe	sedan	sedan	coupe	coupe	sedan	coupe	sedan
vehicle_transmission	auto	manual	manual	auto	manual	auto	auto	auto
vehicle_age	5.0	0.0	10.0	8.0	5.0	3.0	4.0	0.0
at_fault	0	0	0	0	1	1	1	1
insurance_premium	35.0	22.0	77.0	53.0	37.0	22.0	27.0	28.0
party_sobriety	had not been drinking	had not been drinking	had not been drinking	had not been drinking	had not been drinking	impairment unknown	had not been drinking	None
party_drug_physical	None	None	None	None	None	G	None	under drug influence
cellphone_in_use	0.0	0.0	1.0	NaN	1.0	0.0	0.0	0.0
county_city_location	4905	1942	3604	5406	3404	1000	1908	4500
county_location	sonoma	los angeles	san bernardino	tulare	sacramento	fresno	los angeles	shasta
distance	0.0	180.0	0.0	58.0	1584.0	250.0	0.0	249.0
direction	None	south	None	west	north	north	None	west
intersection	1.0	0.0	1.0	0.0	0.0	0.0	1.0	0.0
weather_1	clear	clear	raining	clear	clear	fog	clear	cloudy
location_type	None	highway	None	None	highway	None	None	None
collision_damage	scratch	small damage	small damage	scratch	scratch	small damage	middle damage	severe damage
party_count	2	2	2	2	2	1	2	1
primary_collision_factor	vehicle code violation	vehicle code violation	unknown	vehicle code violation	vehicle code violation	vehicle code violation	vehicle code violation	vehicle code violation
pcf_violation_category	automobile right of way	speeding	unknown	speeding	speeding	improper turning	automobile right of way	dui
type_of_collision	broadside	rear end	sideswipe	rear end	broadside	hit object	broadside	hit object
motor_vehicle_involved_with	other motor vehicle	other motor vehicle	other motor vehicle	other motor vehicle	other motor vehicle	fixed object	other motor vehicle	fixed object
road_surface	dry	dry	wet	dry	dry	dry	dry	wet
road_condition_1	normal	normal	normal	normal	construction	normal	normal	normal
lighting	dusk or dawn	daylight	daylight	dark with street lights	dark with no street lights	dark with no street lights	daylight	dark with no street lights
control_device	functioning	none	functioning	none	none	none	functioning	none
collision_date	2012-01-16	2012-03-23	2012-03-18	2012-01-23	2012-04-02	2012-02-29	2012-04-23	2012-03-16
collision_time	17:17:00	14:44:00	15:17:00	20:42:00	21:09:00	23:30:00	17:23:00	20:45:00
collision_time_2	17.0	14.0	15.0	20.0	21.0	23.0	17.0	20.0
collision_date_2	1.0	3.0	3.0	1.0	4.0	2.0	4.0	3.0

	isna	%
party_drug_physical	98889	96.0
location_type	59921	58.0
direction	27439	27.0
cellphone_in_use	9997	10.0
vehicle_age	2660	3.0
vehicle_transmission	1956	2.0
party_sobriety	1594	2.0
road_surface	783	1.0
road_condition	545	1.0
type_of_collision	595	1.0
pcf_violation_category	1480	1.0
insurance_premium	796	1.0
control_device	476	0.0
lighting	333	0.0
collision_time	110	0.0
collision_hour	110	0.0
motor_vehicle_involved_with	341	0.0
collision_date	0	0.0
vehicle_type	0	0.0
primary_collision_factor	274	0.0
party_count	0	0.0
collision_damage	0	0.0
weather	305	0.0
intersection	349	0.0
distance	0	0.0
county_location	0	0.0
county_city_location	0	0.0
at_fault	0	0.0
collision_month	0	0.0

	f1	LearningTime	PredictingTime
LGBMClassifier	53.68%	0.178852	0.070219
DecisionTreeClassifier	53.35%	1.824887	0.099020
RandomForestClassifier	53.13%	198.689501	6.392586
LogisticRegression	50.77%	0.554329	0.070227
CatBoostClassifier	50.67%	2.074017	0.031247